package com.gy.emailservice.mapper;

import com.gy.emailservice.entity.EmailEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author jayson
 * @version 2.0
 * @date 2021-07-07 16:46
 */
@Mapper
public interface CommonMapper {


    @Select("SELECT\n" +
            "\ta.empNo AS toEmpNo,\n" +
            "\ta.empName AS toEmpName,\n" +
            "\tIFNULL(GROUP_CONCAT( DISTINCT a.ccEmpNo ),'') AS ccEmpNos,\n" +
            "\tIFNULL(GROUP_CONCAT( DISTINCT a.bccEmpNo ),'') AS bccEmpNos,\n" +
            "\tGROUP_CONCAT( a.filePath ) AS filePath,\n" +
            "\ta.moduleName \n" +
            "FROM\n" +
            "\t(\n" +
            "SELECT\n" +
            "\ta.empNo,\n" +
            "\ta.empName,\n" +
            "\t( SELECT empNo FROM oa_emp WHERE fid = f1.empId ) AS ccEmpNo,\n" +
            "\t( SELECT empNo FROM oa_emp WHERE fid = f2.empId ) AS bccEmpNo,\n" +
            "\tCONCAT( d.filePath, #{dateDir}, '\\\\', d.attachName ) AS filePath,\n" +
            "\te.moduleName \n" +
            "FROM\n" +
            "\toa_emp a\n" +
            "\tLEFT JOIN oa_recipient_list b ON a.fid = b.empId\n" +
            "\tLEFT JOIN oa_recipient_list_ccids f1 ON b.fid = f1.recipientListId\n" +
            "\tLEFT JOIN oa_recipient_list_bccids f2 ON b.fid = f2.recipientListId\n" +
            "\tLEFT JOIN oa_attach_name_attr c ON b.fid = c.recipientListId\n" +
            "\tLEFT JOIN oa_attach_name d ON c.attachNameId = d.fid\n" +
            "\tLEFT JOIN oa_mail_module e ON e.fid = d.moduleId \n" +
            "WHERE\n" +
            "\ta.dataState = 1 \n" +
            "\tAND b.dataState = 1 \n" +
            "\tAND c.dataState = 1 \n" +
            "\tAND d.dataState = 1 \n" +
            "\tAND b.dataState = 1 \n" +
            "\tAND e.fid = #{moduleId} \n" +
            "GROUP BY\n" +
            "\ta.empNo,\n" +
            "\ta.empName,\n" +
            "\tf1.empId,\n" +
            "\tf2.empId,\n" +
            "\te.moduleName,\n" +
            "\td.filePath,\n" +
            "\td.attachName \n" +
            "\t) a \n" +
            "GROUP BY\n" +
            "\ta.empNo,\n" +
            "\ta.empName,\n" +
            "\ta.moduleName")
   List<EmailEntity> getEMailInfo(@Param("dateDir") String dateDir,@Param("moduleId") Integer moduleId);
}
